import sqlite3

# 连接到数据库（如果数据库不存在，会自动创建）
conn = sqlite3.connect('students.db')
# 创建一个Cursor对象，用于执行SQL语句
cursor = conn.cursor()
# 创建表
cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    gender TEXT
)
''')
# 提交事务
conn.commit()
# 关闭Cursor对象
cursor.close()
# 关闭Connection对象
conn.close()


# 添加学生
def add_student(name, age, gender):
    conn = sqlite3.connect('students.db')
    cursor = conn.cursor()

    cursor.execute('INSERT INTO students (name, age, gender) VALUES (?, ?, ?)', (name, age, gender))

    conn.commit()  # 提交事务
    cursor.close()  # 关闭Cursor对象
    conn.close()  # 关闭Connection对象


# 查询学生
def get_students():
    conn = sqlite3.connect('students.db')
    cursor = conn.cursor()

    cursor.execute('SELECT * FROM students')
    students = cursor.fetchall()

    cursor.close()
    conn.close()

    return students


# 更新学生
def update_student(student_id, name, age, gender):
    conn = sqlite3.connect('students.db')
    cursor = conn.cursor()

    cursor.execute('UPDATE students SET name=?, age=?, gender=? WHERE id=?', (name, age, gender, student_id))

    conn.commit()
    cursor.close()
    conn.close()


# 删除学生
def delete_student(student_id):
    conn = sqlite3.connect('students.db')
    cursor = conn.cursor()

    cursor.execute('DELETE FROM students WHERE id=?', (student_id,))

    conn.commit()
    cursor.close()
    conn.close()


# 主函数
def main():
    print("学生信息管理系统")
    print("1. 添加学生信息")
    print("2. 查询学生信息")
    print("3. 更新学生信息")
    print("4. 删除学生信息")
    print("5. 退出")

    while True:
        choice = input("请选择操作：")

        if choice == '1':
            name = input("请输入学生姓名：")
            age = int(input("请输入学生年龄："))
            gender = input("请输入学生性别：")
            add_student(name, age, gender)
            print("添加成功！")

        elif choice == '2':
            students = get_students()
            print("学生信息如下：")
            for student in students:
                print(student)

        elif choice == '3':
            student_id = int(input("请输入要更新的学生ID："))
            name = input("请输入新的学生姓名：")
            age = int(input("请输入新的学生年龄："))
            gender = input("请输入新的学生性别：")
            update_student(student_id, name, age, gender)
            print("更新成功！")

        elif choice == '4':
            student_id = int(input("请输入要删除的学生ID："))
            delete_student(student_id)
            print("删除成功！")

        elif choice == '5':
            break

        else:
            print("无效的操作，请重新选择！")


if __name__ == '__main__':
    main()
